Imports System
Imports System.Data
Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types
Imports System.IO
Imports System.Text
  
Module Module1
  
    Sub Main()
  
        'Step 1
        ' Connect to database 
        ' Note: Modify User Id, Password, Data Source as per your database setup
        Dim constr As String = "User Id=scott;Password=tiger;Data Source=orcl9i"
        Dim con As OracleConnection = New OracleConnection(constr)
        con.Open()
        Console.WriteLine("Connected to database!")
  
        ' Step 2
        ' Note: Modify the Source and Destination location 
        ' of the image as per your machine settings
        Dim SourceLoc As String = "D:/Images/photo.jpg"
        Dim DestinationLoc As String = "d:/Images/TestImage.jpg"
  
        ' providing read access to the file 
        Dim fs As FileStream = New FileStream(SourceLoc, FileMode.Open, FileAccess.Read)
  
        ' Create a byte array of file stream length
        Dim ImageData As Byte()
        ReDim ImageData(fs.Length)
  
        'Read block of bytes from stream into the byte array
        fs.Read(ImageData, 0, System.Convert.ToInt32(fs.Length))
  
        'Close the File Stream 
        fs.Close()
  
  
        ' Step 3
        ' Creating Anonymous PL/SQL block string
        Dim block As String = " BEGIN " & _
                              " INSERT INTO testblob (id, photo) values (100, :1) ;" & _
                              " SELECT photo into :2 from testblob WHERE id = 100 ;" & _
                              " end ;"
  
        ' Set command to create Anonymous PL/SQL Block
        Dim cmd As OracleCommand = New OracleCommand()
        cmd.CommandText = block
        cmd.Connection = con
  
        ' Since executing an anonymous PL/SQL block, setting the command type 
        ' as Text instead of StoredProcedure
        cmd.CommandType = CommandType.Text
  
  
        ' Step 4
        ' Setting Oracle parameters
        ' Bind the parameter as OracleDbType.Blob to command for inserting image
        Dim param As OracleParameter = cmd.Parameters.Add("blobtodb", OracleDbType.Blob)
        param.Direction = ParameterDirection.Input
  
        ' Assigning Byte Array to Oracle Parameter
        param.Value = ImageData
  
        ' Bind the parameter as OracleDbType.Blob to command for retrieving the image
        Dim param2 As OracleParameter = cmd.Parameters.Add("blobfromdb", OracleDbType.Blob)
        param2.Direction = ParameterDirection.Output
  
  
        Try
  
            ' Step 5
            ' Execute the Anonymous PL/SQL Block. The anonymous PL/SQL block inserts the 
            ' image to the database and then retrieves the images as an output parameter
            cmd.ExecuteNonQuery()
            Console.WriteLine("Image file inserted to database from " + SourceLoc)
  
  
            ' Step 6 
            ' Save the retrieved image to the DestinationLoc in the file system
            ' Create a byte array
            Dim byteData As Byte()
            Dim Paramvalue As OracleBlob
            Paramvalue = cmd.Parameters(1).Value
  
            ' fetch the value of Oracle parameter into the byte array
            byteData = CType((Paramvalue.Value), Byte())
  
            ' get the length of the byte array
            Dim ArraySize As Integer = New Integer()
            ArraySize = byteData.GetUpperBound(0)
  
            ' Write the Blob data fetched from database to the filesystem at the destination location
            Dim fs1 As FileStream = New FileStream(DestinationLoc, FileMode.OpenOrCreate, FileAccess.Write)
            fs1.Write(byteData, 0, ArraySize)
            fs1.Close()
  
            Console.WriteLine("Image saved to " + DestinationLoc + " successfully !")
            Console.WriteLine("")
            Console.WriteLine("***********************************************************")
            Console.WriteLine("Before running this application again, execute 'Listing 1' ")

            Console.WriteLine("given in 'Create Database Objects' section in the How-to.")
            Console.WriteLine("***********************************************************")
  
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        Finally
            cmd.Dispose()
            con.Close()
            con.Dispose()
        End Try
  
    End Sub
  
End Module

 